﻿/* ---------------------------------------------------
* SQLHelper类
* Author:Zeus2
* Time:2008-07-05
* Last Update:2008-10-15
 * Update 2009-5-14
 * 对存储过程参数调用进行检验
* ---------------------------------------------------
*/

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;

namespace HZ.Data
{
    /// <summary>
    /// 自动关闭连接的SQLHelper类
    /// </summary>
    public class SQLHelper : IDisposable, IDbHelper
    {
        /// <summary>
        /// 经测试，使用连接池连接数据库并频繁登录注销仅仅带来20%性能损失。
        /// </summary>
        [Obsolete("该类已过期")]
        public abstract class SQLHelperStatic
        {
            #region 私有变量

            public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;

            // Hashtable to store cached parameters
            private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

            #endregion

            #region 静态方法

            #region 公用函数

            /// <summary>
            /// Execute a SqlCommand (that returns no resultset)
            /// </summary>
            /// <param name="cmdText">T-SQL command</param>
            /// <returns>an int representing the number of rows affected by the command</returns>
            public static int ExecuteNonQuery(string cmdText)
            {

                return ExecuteNonQuery(CommandType.Text, cmdText, null);
            }
            /// <summary>
            /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
            /// using the provided parameters.
            /// </summary>
            /// <remarks>
            /// e.g.:  
            ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
            /// </remarks>
            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
            /// <param name="commandText">the stored procedure name or T-SQL command</param>
            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
            /// <returns>an int representing the number of rows affected by the command</returns>
            public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
            {

                SqlCommand cmd = new SqlCommand();

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
            /// <summary>
            /// Execute a SqlCommand that returns a resultset
            /// </summary>
            /// <param name="cmdText">T-SQL command</param>
            /// <returns></returns>
            public static IDataReader ExecuteReader(string cmdText)
            {
                return ExecuteReader(CommandType.Text, cmdText, null);
            }
            /// <summary>
            /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
            /// using the provided parameters.
            /// </summary>
            /// <remarks>
            /// e.g.:  
            ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
            /// </remarks>
            /// <param name="connectionString">a valid connection string for a SqlConnection</param>
            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
            /// <param name="commandText">the stored procedure name or T-SQL command</param>
            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
            /// <returns>A SqlDataReader containing the results</returns>
            public static IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
            {
                SqlCommand cmd = new SqlCommand();
                SqlConnection conn = new SqlConnection(ConnectionString);

                // we use a try/catch here because if the method throws an exception we want to 
                // close the connection throw code, because no datareader will exist, hence the 
                // commandBehaviour.CloseConnection will not work
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return (IDataReader)rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }
            /// <summary>
            /// Execute a SqlCommand that returns the first column of the first record
            /// </summary>
            /// <param name="cmdText">T-SQL Command</param>
            /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
            public static object ExecuteScalar(string cmdText)
            {
                return ExecuteScalar(CommandType.Text, cmdText, null);
            }
            /// <summary>
            /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 
            /// using the provided parameters.
            /// </summary>
            /// <remarks>
            /// e.g.:  
            ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
            /// </remarks>
            /// <param name="connectionString">a valid connection string for a SqlConnection</param>
            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
            /// <param name="commandText">the stored procedure name or T-SQL command</param>
            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
            /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
            public static object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
            {
                SqlCommand cmd = new SqlCommand();

                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
            /// <summary>
            /// Execute a SqlCommand that returns DataTable
            /// </summary>
            /// <param name="cmdText">T-SQL command</param>
            /// <returns>A DataTable containing the results</returns>
            public static DataTable ExecuteTable(string cmdText)
            {
                return ExecuteTable(CommandType.Text, cmdText, null);
            }
            /// <summary>
            /// Execute a SqlCommand that returns DataTable
            /// </summary>
            /// <param name="cmdType">CommandType</param>
            /// <param name="cmdText">T-SQL command</param>
            /// <param name="commandParameters">an array of IParamters used to execute the command</param>
            /// <returns>A DataTable containing the results</returns>
            public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
            {
                SqlCommand cmd = new SqlCommand();

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    return dt;
                }
            }
            /// <summary>
            /// Execute a SqlCommand that returns DataSet
            /// </summary>
            /// <param name="cmdText">T-SQL command</param>
            /// <returns>A DataSet containing the results</returns>
            public static DataSet ExecuteDataSet(string cmdText)
            {
                return ExecuteDataSet(CommandType.Text, cmdText, null);
            }
            /// <summary>
            /// Execute a SqlCommand that returns DataSet
            /// </summary>
            /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
            /// <param name="cmdText">the stored procedure name or T-SQL command</param>
            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
            /// <returns></returns>
            public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
            {
                SqlCommand cmd = new SqlCommand();
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);
                    return ds;
                }
            }

            #endregion

            #region 参数缓存


            /// <summary>
            /// add parameter array to the cache
            /// </summary>
            /// <param name="cacheKey">Key to the parameter cache</param>
            /// <param name="cmdParms">an array of SqlParamters to be cached</param>
            public static void CacheParameters(string cacheKey, params IDataParameter[] commandParameters)
            {
                parmCache[cacheKey] = commandParameters;
            }

            /// <summary>
            /// Retrieve cached parameters
            /// </summary>
            /// <param name="cacheKey">key used to lookup parameters</param>
            /// <returns>Cached SqlParamters array</returns>
            public static IDataParameter[] GetCachedParameters(string cacheKey)
            {
                SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

                if (cachedParms == null)
                    return null;

                SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

                for (int i = 0, j = cachedParms.Length; i < j; i++)
                    clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

                return clonedParms;
            }

            #endregion

            #region 私有函数

            /// <summary>
            /// Prepare a command for execution
            /// </summary>
            /// <param name="cmd">SqlCommand object</param>
            /// <param name="conn">SqlConnection object</param>
            /// <param name="trans">SqlTransaction object</param>
            /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
            /// <param name="cmdText">Command text, e.g. Select * from Products</param>
            /// <param name="cmdParms">SqlParameters to use in the command</param>
            private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
            {

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;

                if (trans != null)
                    cmd.Transaction = trans;

                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (IDataParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }

            #endregion

            #endregion
        }

        #region 非静态方法

        public static string ConnectionString = null;

        #region 构造函数

        public SQLHelper()
        {
            if (ConnectionString != null)
            {
                // con = new SqlConnection(ConnectionString);
                return;
            }
            string constr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
            if (string.IsNullOrEmpty(constr))
            {
                throw new Exception("数据库连接不存在,请检查App.config文件");
            }
            else
            {
                // con = new SqlConnection(constr);
                ConnectionString = constr;
            }
        }
        public SQLHelper(string connectstring)
        {
            // con = new SqlConnection(connectstring);
            ConnectionString = connectstring;
        }

        #endregion

        #region 公用函数

        /// <summary>
        /// 执行一个T-SQL (不返回结果集)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回一个 int 表示这个命令所影响的行数</returns>
        public int ExecuteNonQuery(string cmdText)
        {

            return ExecuteNonQuery(CommandType.Text, cmdText, null);
        }
        /// <summary>
        ///执行一个T-SQL命令(可以含参数)(不返回结果集) 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandType">命令类型 (存储过程, 文本, 等)</param>
        /// <param name="commandText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回执行这个命令所影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();
            if (_BeginTrans)
            {
                PrepareCommand(cmd, null, Transaction, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            else
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }


        }
        /// <summary>
        /// 执行一个T-SQL命令(返回结果集)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string cmdText)
        {
            return ExecuteReader(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回结果集)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks> 
        /// <param name="commandType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="commandText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>一个包含查询结果的 DataReader</returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionString);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return (IDataReader)rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
        /// <summary>
        /// 执行一个命令(返回结果集的第一行第一列结果)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回一个可以转换成预期类型的object对象</returns>
        public object ExecuteScalar(string cmdText)
        {
            return ExecuteScalar(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个命令(返回结果集的第一行第一列结果)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks> 
        /// <param name="commandType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="commandText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回一个可以转换成预期类型的object对象</returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            if (_BeginTrans)
            {
                PrepareCommand(cmd, null, Transaction, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
            else
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataTable)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回包含结果的DataTable </returns>
        public DataTable ExecuteTable(string cmdText)
        {
            return ExecuteTable(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataTable)
        /// </summary>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="cmdText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回包含结果的DataTable</returns>
        public DataTable ExecuteTable(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            if (_BeginTrans)
            {
                PrepareCommand(cmd, null, Transaction, cmdType, cmdText, commandParameters);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                return dt;

            }
            else
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    return dt;
                }


        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataSet)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回一个 DataSet</returns>
        public DataSet ExecuteDataSet(string cmdText)
        {
            return ExecuteDataSet(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataSet)
        /// </summary>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="cmdText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回一个 DataSet</returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                return ds;
            }

        }


        public int ExecuteNonQuery(IDbTransaction trans, CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, (SqlConnection)trans.Connection, (SqlTransaction)trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public int ExecuteNonQuery(IDbConnection conn, CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, (SqlConnection)conn, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
        {
            if (_Trans != null && _BeginTrans)
            {
                cmd.Transaction = _Trans;
                cmd.Connection = _Trans.Connection;
            }
            else
            {

                cmd.Connection = conn;

                if (cmd.Connection.State != ConnectionState.Open)
                    conn.Open();


            }
            cmd.CommandText = cmdText;



            cmd.CommandType = cmdType;



            // 参数检验，忽略多于的参数，跑出缺少参数
            if (cmdParms != null)
            {
                if (cmdType == CommandType.StoredProcedure)
                {
                    IList<string> paras = null;

                    if (!SP_Paras.Keys.Contains(cmdText))
                    {
                        string sql = string.Format(@"SELECT A.name Para FROM sys.syscolumns A,sys.systypes B WHERE id = object_id('{0}') AND A.xtype = B.xtype ORDER BY A.colid", cmdText);
                        paras = new List<string>();
                        DataTable dt = ExecuteTable(sql);
                        foreach (DataRow item in dt.Rows)
                            paras.Add(item["Para"].ToString());
                        SP_Paras.Add(cmdText, paras);
                    }
                    else
                    {
                        paras = SP_Paras[cmdText];
                    }
                    if (cmdParms.Length > paras.Count)
                    {
                        foreach (IDataParameter parm in cmdParms)
                        {
                            if (!paras.Contains(parm.ParameterName))
                            {
                                continue;
                            }
                            cmd.Parameters.Add(parm);
                        }

                    }
                    else
                    {
                        foreach (IDataParameter parm in cmdParms)
                            cmd.Parameters.Add(parm);
                    }
                }
                else
                {
                    foreach (IDataParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }


            }
        }

        #region 存储过程缓存

        private static IDictionary<string, IList<string>> _SP_Paras = new Dictionary<string, IList<string>>();

        private IDictionary<string, IList<string>> SP_Paras
        {
            get
            {
                return _SP_Paras;
            }
        }

        #endregion



        private SqlTransaction _Trans = null;

        /// <summary>
        /// 是否开始了事务处理
        /// </summary>
        private bool _BeginTrans = false;
        private SqlTransaction Transaction
        {
            get
            {

                return _Trans;
            }
        }

        /// <summary>
        /// 在当前上下文开始一个事务
        /// </summary>
        public void BeginTrans()
        {
            _BeginTrans = true;
            if (_Trans == null)
            {
                IDbConnection conn = CreateConnection();
                conn.Open();
                _Trans = (SqlTransaction)conn.BeginTransaction();

            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTrans()
        {
            _BeginTrans = false;
            _Trans.Commit();
            _Conn.Close();
            _Trans = null;
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollTrans()
        {
            _BeginTrans = false;
            _Trans.Rollback();
            _Conn.Close();
            _Trans = null;
        }

        /// <summary>
        /// 关闭连接并释放资源
        /// </summary>
        void IDbHelper.Close()
        {

        }

        ~SQLHelper()
        {

        }



        #region IDisposable Members
        void IDisposable.Dispose()
        {
            if (_Conn != null)
            {
                _Conn.Close();
            }

        }
        #endregion

        private IDbConnection _Conn = null;
        public IDbConnection CreateConnection()
        {
            if (_Conn == null)
            {
                _Conn = new SqlConnection(ConnectionString);
            }
            return _Conn;
        }

        #endregion

    }
}
